2.4 Practice Working with Conditional Aggregate Functions

Use the Music Concert Attendance 2017.xlsx workbook file to complete the practice problems.

Part 1

1. Create range names for all the columns of data using Create from Selection in Defined Names for ConcertAttendanceData sheet.

2. On a new sheet named "Deduplicated Data" remove duplicates from the columns of data that are dimensions (not measures) (Artist/Event, Venue, City/State, Promoters).

3. How much money has each Artist/Event generated? Return the SUM, AVERAGE, COUNT for each artist. Also return the SUM, AVERAGE, COUNT, MIN, MAX, 8th Smallest, 5th largest for the entire data set. Put in new sheet named "SumIF and Regular."

Part 2

4. Syntax Variations in SumIf- What were the gross sales for each City/State using SumIf? What were the gross sales in Texas using SumIf? How many venues were in Texas using Countif? What were the gross sales for City/States starting with "M" using SumIF? How many venues were in City/States starting with "M" using CountIf? Put on new sheet named "Syntax Variations."

Part 3

5. Paste Transpose, Sumifs- What were gross sales for promoters in the city/states using SumIfs? Put in new sheet named "TableReport."

Part 4

6. Data Table 2 Sumif, Countif, Syntax Variations- Use MostCoveredSongData sheet, name the column ranges using Create from Selection in Defined Names. Use SumIF to find the following: How many different songs were covered for each artist? How many total covers were produced for each song of an artist? How many covers was Doris Day involved in? How many covers was Elvis Presley involved in?